package com.liuxing.calculation;

import com.liuxing.entity.DatabaseConfig;
import com.liuxing.service.DatabaseConfigService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * desc: 执行sql的计算引擎
 *
 * @author 刘磊
 * @since 2020/3/16
 */
@Slf4j
@Component("sqlCalculationEngine")
public class SqlICalculationEngine extends ICalculationEngine {

    static {
        List<String> subDrivers = new ArrayList<>();
        subDrivers.add("oracle.jdbc.driver.OracleDriver");
        subDrivers.add("oracle.jdbc.OracleDriver");
        subDrivers.add("com.sybase.jdbc2.jdbc.SybDriver");
        subDrivers.add("net.sourceforge.jtds.jdbc.Driver");
        // MS driver for Sql Server 2000
        subDrivers.add("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        // MS driver for Sql Server 2005
        subDrivers.add("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        subDrivers.add("weblogic.jdbc.sqlserver.SQLServerDriver");
        subDrivers.add("com.informix.jdbc.IfxDriver");
        subDrivers.add("org.apache.derby.jdbc.ClientDriver");
        subDrivers.add("org.apache.derby.jdbc.EmbeddedDriver");
        subDrivers.add("com.mysql.cj.jdbc.Driver");
        subDrivers.add("org.postgresql.Driver");
        subDrivers.add("org.hsqldb.jdbcDriver");
        subDrivers.add("org.h2.Driver");
        // 加载必要的数据库驱动
        subDrivers.forEach(driver -> {
            try {
                Class.forName(driver);
                log.info("Driver [{}] loaded.", driver);
            } catch (Exception ignored){}
        });
    }

    @Autowired
    private DatabaseConfigService databaseConfigService;

    @Override
    public Object calculate(String content, Map<String, Object> param) {
        Integer databaseId = (Integer) param.get("databaseId");
        Connection connection = getConnection(databaseId);
        Statement statement = null;
        ResultSet resultSet = null;
        if (connection != null) {
            if (content != null && content.length() > 0) {
                try {
                    // 替换sql中的待传变量
                    for (Map.Entry<String, Object> entry : param.entrySet()) {
                        String rk = "[" + entry.getKey() + "]";
                        if (content.contains(rk)) {
                            content = content.replace(rk, "'" + entry.getValue().toString() + "'");
                        }
                    }
                    if (content.contains("[") && content.contains("]")) {
                        log.error("缺少必要的参数");
                        return null;
                    }
                    // 执行sql
                    statement = connection.createStatement();
                    resultSet = statement.executeQuery(content);
                    Map<String, List<Object>> result = new HashMap<>();
                    // 获取结果字段名
                    String[] head = getRowHead(resultSet);
                    for (String h : head) {
                        result.put(h, new ArrayList<>());
                    }
                    // 把结果集中的数据按列转换成list存储到map中
                    while (resultSet.next()) {
                        for (int i = 0; i < head.length; i++) {
                            String h = head[i];
                            result.get(h).add(resultSet.getObject(i));
                        }
                    }
                    return result;
                } catch (Exception e) {
                    log.error("SQL计算引擎出现异常：", e);
                } finally {
                    closeConnection(resultSet, statement, connection);
                }
            }
        }
        return null;
    }

    protected void setParentResultToParamMap(Integer parentId, Object obj, Map<String, Object> param) {
        Map<String, List<Object>> rst = (Map<String, List<Object>>) obj;
        param.putAll(rst);
    }

    /**
     * 根据数据库配置的对应数据源获取数据库配置
     * @param databaseId 数据源
     * @return 数据库连接
     */
    private Connection getConnection(Integer databaseId) {
        try {
            DatabaseConfig config = databaseConfigService.selectByPrimaryKey(databaseId);
            // 数据库密码没有进行加密处理
            return DriverManager.getConnection(config.getDbUrl(), config.getDbUsername(), config.getDbPassword());
        } catch (SQLException e) {
            log.error("获取数据库连接时出现异常：", e);
        }
        return null;
    }

    private void closeConnection(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ignored) {
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ignored) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ignored) {
            }
        }
    }

    private String[] getRowHead(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cnt = metaData.getColumnCount();
        String[] rst = new String[cnt];
        for (int i = 0; i < cnt; i++) {
            rst[i] = metaData.getColumnName(i);
        }
        return rst;
    }
}
